package com.example.sql_valid.service;

import com.example.sql_valid.constant.ErrorInfo;
import com.example.sql_valid.constant.FilePath;
import com.example.sql_valid.constant.RulesInfo;
import com.example.sql_valid.controller.Response.ValidationResponse;
import com.example.sql_valid.controller.SQLValidatorController;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.index.CreateIndex;
import net.sf.jsqlparser.statement.create.sequence.CreateSequence;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.merge.Merge;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * SQL验证服务类，用于验证给定的SQL文件列表是否符合指定的schema规范
 */
@Service
public class SQLValidatorServiceV1 {

    private static final Logger logger = LoggerFactory.getLogger(SQLValidatorController.class);


    /**
     * 验证给定的SQL文件列表是否符合指定的schema规范
     *
     * @param files           待验证的SQL文件列表
     * @param schema          指定的schema名称
     * @param checkTablespace 是否检查表空间
     * @return 包含验证结果和错误文件列表的ValidationResponse对象
     */
    public ValidationResponse SchemaValid(List<MultipartFile> files, String schema, boolean checkTablespace) {
        StringBuilder result = new StringBuilder();
        //文件列表
        List<String> errorFiles = new ArrayList<>();
        List<String> validFiles = new ArrayList<>();
        Pattern tablespacePattern = Pattern.compile("\\btablespace\\b", Pattern.CASE_INSENSITIVE);
        for (MultipartFile file : files) {
            String fileName = file.getOriginalFilename();
//            错误文件列表
            StringBuilder fileErrors = new StringBuilder();
//            评审文件列表
            StringBuilder validSql = new StringBuilder();

            String validSqlInfo = "";

            try {
                List<String> lines = readLines(file);

                String rawSQL = String.join("\n", lines);

                String cleanSQL = removeComments(rawSQL);

                List<String> sqlStatements = splitSQLStatements(cleanSQL);

                for (String sqlStatementOld : sqlStatements) {
                    boolean schemaMis = false;
                    boolean schemaNotMatch = false;
                    boolean missingTablespace = false;
//                    是否需要评审
                    boolean isValid = false;
                    sqlStatementOld = sqlStatementOld.trim();
                    if (sqlStatementOld.toLowerCase().contains("add constraint") || sqlStatementOld.toLowerCase().contains("add primary")) {
                        //取出这条语句中的表名
                        String regex = "(?i)using\\s+index\\s+tablespace";
                        Pattern pattern = Pattern.compile(regex, Pattern.DOTALL);
                        if (pattern.matcher(sqlStatementOld.toLowerCase()).find()) {
                            String name = sqlStatementOld.split(" ")[2];
                            logger.info("name: " + name);
                            String[] schemaNames = name.split("\\.");
                            if (schemaNames.length <= 1) {
                                schemaMis = true;
                            } else {
                                String schemaName = schemaNames[0].replace("\"", "");
                                logger.info("schema名字: " + schemaName);
                                if (!schemaName.equalsIgnoreCase(schema)) {
                                    schemaNotMatch = true;
                                }
                            }
                            //取出表空间名 ALTER TABLE bfmcs.your_table ADD CONSTRAINT your_table_pk PRIMARY KEY (ID) using index tablespace your_tablespace;
                            if (checkTablespace && !tablespacePattern.matcher(sqlStatementOld.toLowerCase()).find()) {
                                missingTablespace = true;
                            }
                        } else {
                            //fileErrors.append("文件: ").append(fileName).append(ErrorInfo.MISS_USING_TABLESPACE).append("\n").append(sqlStatementOld).append("\n");
                            mapFileError(fileErrors, ErrorInfo.MISS_USING_TABLESPACE, fileName, sqlStatementOld);
                        }


                    } else {
                        try {
                            String sqlStatement = sqlStatementOld;

                            sqlStatement = sqlStatementOld.replaceAll("ENABLE", "").replaceAll("GLOBAL", "");

                            Statement statement = CCJSqlParserUtil.parse(sqlStatement);


                            if (statement instanceof Select) {
                                Select select = (Select) statement;
                                Map<String, Boolean> map = findTable(select, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");
                                //处理create表语句
                            } else if (statement instanceof CreateTable) {
                                CreateTable createTable = (CreateTable) statement;
                                Map<String, Boolean> map = findTable(createTable, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");
                                missingTablespace = vaildTableSpace(checkTablespace, sqlStatement);
                                //建表语句建表体提取
                                List<ColumnDefinition> columnDefinitions = createTable.getColumnDefinitions();
                                StringBuilder stringBuilder = new StringBuilder();
                                for (ColumnDefinition column : columnDefinitions) {
                                    String[] targetDataTypes = {"varchar2", "char", "raw","varchar","int"};
                                    String columnDataType = column.getColDataType().getDataType();
                                    for (String targetType : targetDataTypes) {
                                        if (columnDataType.equalsIgnoreCase(targetType)) {
                                            List<String> argumentsStringList = column.getColDataType().getArgumentsStringList();
                                            if(argumentsStringList == null || argumentsStringList.isEmpty()){
                                                stringBuilder.append("\n"+column);
                                            }
                                            break; // 找到匹配项后退出循环
                                        }
                                    }

                                }
                                if(stringBuilder.length()>0) {
                                    mapFileError(fileErrors, ErrorInfo.CREATE_TABLE_COLUMN_MISS_ARGS + "\n异常字段：" + stringBuilder, fileName, sqlStatementOld);
                                }
                                //处理createIndex表语句
                            } else if (statement instanceof CreateIndex) {
                                CreateIndex createIndex = (CreateIndex) statement;
                                //获取索引名，带schema
                                String name = createIndex.getIndex().getName();
                                //分割出schema
                                String[] schemaNames = name.split("\\.");

                                if (schemaNames.length <= 1) {
                                    schemaMis = true;
                                } else {
                                    String schemaName = schemaNames[0].replace("\"", "");
                                    if (!schemaName.equalsIgnoreCase(schema)) {
                                        schemaNotMatch = true;
                                    }
                                }
                                missingTablespace = vaildTableSpace(checkTablespace, sqlStatement);

                                //如果创建索引语句后没有增加 online 关键字，则添加到报错内容中
                                if (!createIndex.toString().toUpperCase().endsWith("ONLINE")) {
                                    //fileErrors.append("文件: ").append(fileName).append(ErrorInfo.MISS_ONLINE).append("\n").append(sqlStatementOld).append("\n");
                                    mapFileError(fileErrors, ErrorInfo.MISS_ONLINE, fileName, sqlStatementOld);
                                }
                            } else if (statement instanceof CreateSequence) {
                                CreateSequence createSequence = (CreateSequence) statement;

                                //获取索引名，带schema
                                String name = createSequence.getSequence().getSchemaName();

                                if (null == name) {
                                    schemaMis = true;
                                } else if (!name.equalsIgnoreCase(schema)) {
                                    schemaNotMatch = true;

                                }
                            } else if (statement instanceof Update) {
                                Update update = (Update) statement;

                                Map<String, Boolean> map = findTable(update, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");

                            } else if (statement instanceof Delete) {
                                Delete delete = (Delete) statement;

                                Map<String, Boolean> map = findTable(delete, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");

                                //delete语句中没有where条件，则添加到评审sql
                                if (!delete.toString().toUpperCase().contains("WHERE")) {
                                    mapFileError(fileErrors, RulesInfo.DELETE_01, fileName, sqlStatementOld);
                                }

                            } else if (statement instanceof Insert) {
                                Insert insert = (Insert) statement;

                                Map<String, Boolean> map = findTable(insert, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");

                            } else if (statement instanceof Drop) {
                                Drop drop = (Drop) statement;
                                Map<String, Boolean> map = findTable(drop, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");

                                validSqlInfo = RulesInfo.DROP_01;
                                isValid = true;
                            } else if (statement instanceof Alter) {
                                Alter alter = (Alter) statement;


                                String tableName = alter.getTable().toString();

                                String[] schemaNames = tableName.split("\\.");

                                if (schemaNames.length <= 1) {
                                    schemaMis = true;
                                } else {
                                    String schemaName = schemaNames[0].replace("\"", "");
                                    if (!schemaName.equalsIgnoreCase(schema)) {
                                        schemaNotMatch = true;
                                    }
                                }

                                if (alter.toString().toUpperCase().contains("ADD")) {
                                    validSqlInfo = RulesInfo.INDEX_01;
                                    isValid = true;
                                }
                                if (alter.toString().toUpperCase().contains("MODIFY")) {
                                    validSqlInfo = RulesInfo.INDEX_02;
                                    isValid = true;
                                }
                                if (alter.toString().toUpperCase().contains("RENAME")) {
                                    validSqlInfo = RulesInfo.RENAME_01;
                                    isValid = true;
                                }
                            } else if (statement instanceof CreateView) {
                                String fullyQualifiedName = ((CreateView) statement).getView().getFullyQualifiedName();

                                String[] schemaNames = fullyQualifiedName.split("\\.");

                                if (schemaNames.length <= 1) {
                                    schemaMis = true;
                                } else {
                                    String schemaName = schemaNames[0].replace("\"", "");
                                    if (!schemaName.equalsIgnoreCase(schema)) {
                                        schemaNotMatch = true;
                                    }
                                }
                                Select select = ((CreateView) statement).getSelect();
                                Map<String, Boolean> map = findTable(select, schema);
                                if (schemaMis != true) {
                                    schemaMis = map.get("schemaMis");
                                }
                                if (schemaNotMatch != true) {
                                    schemaNotMatch = map.get("schemaNotMatch");
                                }

                            } else if (statement instanceof Merge) {
                                Merge merge = (Merge) statement;
                                Map<String, Boolean> map = findTable(merge, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");

                            } else if (statement instanceof Truncate) {
                                Truncate truncate = (Truncate) statement;
                                Map<String, Boolean> map = findTable(truncate, schema);
                                schemaMis = map.get("schemaMis");
                                schemaNotMatch = map.get("schemaNotMatch");
                                validSqlInfo = RulesInfo.TRUNCATE_01;
                                isValid = true;

                            }

                        } catch (JSQLParserException e) {
                            // fileErrors.append("文件: ").append(fileName).append(ErrorInfo.SQL_VALID_ERROR).append(sqlStatementOld).append("\n");
                            mapFileError(fileErrors, ErrorInfo.SQL_VALID_ERROR, fileName, sqlStatementOld);
                            logger.error("SQL解析失败: " + sqlStatementOld, e);
                        }

                    }


                    if (schemaNotMatch) {
                        //fileErrors.append("文件: ").append(fileName).append(ErrorInfo.ERROR_SCHEMA).append("\n").append(sqlStatementOld).append("\n");
                        mapFileError(fileErrors, ErrorInfo.ERROR_SCHEMA, fileName, sqlStatementOld);
                    }
                    if (schemaMis) {
                        //fileErrors.append("文件: ").append(fileName).append(ErrorInfo.MISS_SCHEMA).append("\n").append(sqlStatementOld).append("\n");
                        mapFileError(fileErrors, ErrorInfo.MISS_SCHEMA, fileName, sqlStatementOld);
                    }
                    if (missingTablespace) {
                        // fileErrors.append("文件: ").append(fileName).append(ErrorInfo.MISS_TABLESPACE).append("\n").append(sqlStatementOld).append("\n");
                        mapFileError(fileErrors, ErrorInfo.MISS_TABLESPACE, fileName, sqlStatementOld);
                    }
                    if (isValid) {
                        validSql.append(validSqlInfo).append("\n").append(sqlStatementOld).append("\n");
                    }
                }

                if (fileErrors.length() == 0) {
                    result.append("文件: ").append(fileName).append(" - 验证通过\n");
                }

            } catch (Exception e) {
                fileErrors.append("文件: ").append(" - 读取失败\n");
            }

            if (fileErrors.length() > 0) {
                result.append(fileErrors);
                try {
                    Path errorFilePath = Paths.get(FilePath.errorFiles, fileName.replace(".sql", "_error.sql"));
                    Files.createDirectories(errorFilePath.getParent());

                    Files.write(errorFilePath, fileErrors.toString().getBytes(StandardCharsets.UTF_8));
                    errorFiles.add(errorFilePath.getFileName().toString());  // 添加文件名到列表
//                    result.append("错误SQL已保存到: ").append(errorFilePath.toAbsolutePath()).append("\n");
                    logger.info("错误SQL已保存到: " + errorFilePath.toAbsolutePath());
                } catch (IOException e) {
                    result.append("保存错误文件时出错: ").append(e.getMessage()).append("\n");
                    logger.error("保存错误文件时出错: " + e.getMessage(), e);
                }
            }

            if (validSql.length() > 0) {
                try {
                    Path validFilePath = Paths.get(FilePath.validFiles, fileName.replace(".sql", "_valid.sql"));
                    Files.createDirectories(validFilePath.getParent());

                    Files.write(validFilePath, validSql.toString().getBytes(StandardCharsets.UTF_8));
                    validFiles.add(validFilePath.getFileName().toString());  // 添加文件名到列表
//                    result.append("错误SQL已保存到: ").append(errorFilePath.toAbsolutePath()).append("\n");
                    logger.info("验证SQL已保存到: " + validFilePath.toAbsolutePath());
                } catch (IOException e) {
                    result.append("保存验证文件时出错: ").append(e.getMessage()).append("\n");
                    logger.error("保存验证文件时出错: " + e.getMessage(), e);
                }
            }


        }


        ValidationResponse response = new ValidationResponse();
        response.setResult(result.toString());
        response.setErrorFiles(errorFiles);
        response.setValidFiles(validFiles);

        return response;
    }

    private List<String> readLines(MultipartFile file) throws IOException {
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream(), StandardCharsets.UTF_8))) {
            return reader.lines()
                    .map(this::removeBOM)  // 移除BOM字符
                    .collect(Collectors.toList());
        }
    }


    /**
     * 移除字符串开头的BOM字符（如果有）
     *
     * @param line 待处理的字符串
     * @return 移除BOM字符后的字符串
     */
    private String removeBOM(String line) {
        if (line != null && line.startsWith("\uFEFF")) {
            return line.substring(1);
        }
        return line;
    }

    /**
     * 从SQL语句中移除注释
     *
     * @param sql 包含注释的SQL语句
     * @return 移除注释后的SQL语句
     */
    private String removeComments(String sql) {
        // 移除单行注释
        sql = sql.replaceAll("--.*?(\r?\n|$)", "");
        // 移除多行注释
        sql = sql.replaceAll("(?s)/\\*[^*]*\\*+([^/][^*]*\\*+)*\\*/", "");
        // 移除多行注释，使用 DOTALL 模式
        sql = sql.replaceAll("(?s)/\\*.*?\\*/", "");
        sql = sql.replaceAll("^\\s*(?=$|\\r?\\n)", "");

        return sql.trim();
    }

    private List<String> splitSQLStatements(String sql) {
        // 使用分号加换行符分割SQL语句
        return Arrays.asList(sql.split(";\\s*\\n*"));
    }

    /**
     * 在给定的SQL语句中查找表名，并判断表名是否缺失或不符合指定的schema。
     *
     * @param statement SQL语句对象
     * @param schema    指定的schema名称
     * @return 包含是否表名缺失和是否表名不符合schema的布尔值映射
     */
    private Map<String, Boolean> findTable(Statement statement, String schema) {
        Boolean schemaMis = false;
        Boolean schemaNotMatch = false;

        Map<String, Boolean> stringBooleanHashMap = new HashMap<>();

        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();

        List<String> tableList = tablesNamesFinder.getTableList(statement);

        for (String s : tableList) {
            String[] schemaNames = s.split("\\.");

            if (schemaNames.length <= 1) {
                schemaMis = true;
            } else {
                String schemaName = schemaNames[0].replace("\"", "");
                if (!schemaName.equalsIgnoreCase(schema)) {
                    schemaNotMatch = true;
                }
            }
        }

        stringBooleanHashMap.put("schemaMis", schemaMis);
        stringBooleanHashMap.put("schemaNotMatch", schemaNotMatch);

        return stringBooleanHashMap;

    }

    /**
     * 判断SQL语句中是否缺少表空间
     *
     * @param checkTablespace 是否需要检查表空间
     * @param sqlStatement    待检查的SQL语句
     * @return 如果需要检查表空间且SQL语句中不包含表空间关键字，则返回true；否则返回false
     */
    private Boolean vaildTableSpace(boolean checkTablespace, String sqlStatement) {
        Boolean missingTablespace = false;

        Pattern tablespacePattern = Pattern.compile("\\btablespace\\b", Pattern.CASE_INSENSITIVE);
        if (checkTablespace && !tablespacePattern.matcher(sqlStatement).find()) {
            missingTablespace = true;
        }

        return missingTablespace;
    }


    private void mapFileError(StringBuilder fileErrors, String errorInfo, String fileName, String sqlStatementOld) {
        fileErrors.append("文件: ").append(fileName).append(errorInfo).append("\n").append("SQL语句为：\n"+sqlStatementOld).append("\n");
    }


    public static List<String> readSqlFile(MultipartFile file) {
        List<String> sqlStatements = new ArrayList<>();
        StringBuilder sqlBuffer = new StringBuilder();
        boolean inMultiLineComment = false;

        try (BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream()))) {
            String line;
            while ((line = reader.readLine()) != null) {
                line = line.trim();

                // 处理多行注释
                if (inMultiLineComment) {
                    if (line.endsWith("*/")) {
                        inMultiLineComment = false;
                        line = line.substring(0, line.lastIndexOf("*/")).trim();
                    } else {
                        continue;
                    }
                } else if (line.startsWith("/*")) {
                    if (!line.endsWith("*/")) {
                        inMultiLineComment = true;
                    }
                    line = line.substring(line.indexOf("*/") + 2).trim();
                }

                // 处理单行注释
                if (line.startsWith("--")) {
                    continue;
                }

                // 移除行内注释
                int commentIndex = line.indexOf("--");
                if (commentIndex != -1) {
                    line = line.substring(0, commentIndex).trim();
                }

                // 如果行不是空的，添加到缓冲区
                if (!line.isEmpty()) {
                    sqlBuffer.append(line).append(" ");
                }

                // 如果遇到分号，则认为是一个完整的SQL语句
                if (line.endsWith(";")) {
                    sqlStatements.add(sqlBuffer.toString().trim());
                    sqlBuffer.setLength(0); // 清空缓冲区
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return sqlStatements;
    }

}
